import json
import re

import numpy as np
import pandas as pd
import requests

pd.set_option('display.max_rows', None)  # 设置显示最大行数为无限
pd.set_option('display.max_columns', None)  # 设置显示最大列数为无限
pd.set_option('display.width', None)  # 自动检测控制台的宽度

url = "https://fund.eastmoney.com/data/rankhandler.aspx"
params = {
    "op": "ph",
    "dt": "kf",
    "ft": "all",
    "rs": "",
    "gs": "0",
    "sc": "1nzf",
    "st": "desc",
    "sd": "2023-04-10",
    "ed": "2024-04-10",
    "qdii": "",
    "tabSubtype": ",,,,,",
    "pi": "1",
    "pn": "15397",
    "dx": "1",
    "v": "0.07987465766464519"
}

headers = {
    "Accept": "*/*",
    "Accept-Encoding": "gzip, deflate, br, zstd",
    "Accept-Language": "zh-CN,zh;q=0.9",
    "Connection": "keep-alive",
    "Cookie": "b-user-id=76493389-57eb-4c03-9853-e96693018299; ASP.NET_SessionId=cwwxwpe1wjhn1qhk4c4tjct4; st_si=815258044184; st_asi=delete; st_pvi=97247613210367; st_sp=2024-04-10%2013%3A06%3A08; st_inirUrl=; st_sn=7; st_psi=20240410132319999-112200312936-1176257744",
    "Host": "fund.eastmoney.com",
    "Referer": "https://fund.eastmoney.com/data/fundranking.html",
    "Sec-Ch-Ua": "\"Google Chrome\";v=\"123\", \"Not:A-Brand\";v=\"8\", \"Chromium\";v=\"123\"",
    "Sec-Ch-Ua-Mobile": "?0",
    "Sec-Ch-Ua-Platform": "\"Windows\"",
    "Sec-Fetch-Dest": "script",
    "Sec-Fetch-Mode": "no-cors",
    "Sec-Fetch-Site": "same-origin",
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36"
}

response = requests.get(url, params=params, headers=headers)

if response.status_code == 200:
    response_text = response.text
    # print(response_text)

    # 使用正则表达式提取datas键对应的值
    pattern = r'(\[.*\])'
    match = re.search(pattern, response_text, re.DOTALL)
    datas_str = ""
    if match:
        datas_str = match.group(1)

    # 将字符串转换为JSON对象
    # print(datas_str)
    datas_json = json.loads(datas_str)
    # print(datas_json)

    # 指定列名
    columns = [f'col{i}' for i in range(1, 26)]
    print(columns)
    columns1 = [
        'fund_code', 'fund_name', 'fund_short_name', 'maturity_date',
        'current_nav', 'total_nav', 'daily_change', 'seven_days_yield',
        'thirty_days_yield', 'sixty_days_yield', 'ninety_days_yield',
        'purchase_fee', 'redemption_fee', 'issue_date', 'management_fee_rate',
        'sales_load_type', 'purchase_min_amount', 'redeem_min_amount', 'net_asset_value',
        'other_field1', 'other_field2', 'other_field3', 'other_field4', 'other_field5', 'other_field6'
    ]
    columns = [
        'fund_code', 'fund_name', 'fund_short_name', 'maturity_date',
        'current_nav', 'total_nav', 'daily_change', 'one_week_yield',
        'one_month_yield', 'three_month_yield', 'six_month_yield', 'one_year_yield',
        'two_year_yield', 'three_year_yield', 'year_to_date_yield', 'since_inception_yield',
        'issue_date', 'ji_shu1', 'self_range_yield', 'purchase_fee_percent',
        'purchase_fee_discount', 'ji_shu2', 'purchase_fee_discount2', 'ji_shu3', 'other_field'
    ]
    print(columns)

    # 将数据分割并转换为 DataFrame
    df = pd.DataFrame([data.split(',') for data in datas_json], columns=columns)
    # 重设索引，从1开始
    df.index += 1
    # 根据需要，将日期和百分比类型的数据转换为相应的格式
    # df['maturity_date'] = pd.to_datetime(df['maturity_date'])
    # df[['seven_days_yield', 'thirty_days_yield', 'sixty_days_yield', 'ninety_days_yield']] = df[
    #                                                                                              ['seven_days_yield',
    #                                                                                               'thirty_days_yield',
    #                                                                                               'sixty_days_yield',
    #                                                                                               'ninety_days_yield']].replace(
    #     '%', '', regex=True).astype(float) / 100

    # 显示 DataFrame
    # 查看列的数据类型

    # 写入csv
    # df.reset_index(drop=True, inplace=True)

    # df.to_csv('output.csv', sep=',', index=False, header=False,encoding='gbk')
    # df.to_csv('output1.csv', sep=',', index=True, header=False,encoding='gbk')
    # df=df[df['current_nav']]

    # 根据需要，将日期和百分比类型的数据转换为相应的格式
    # 假设df是你的DataFrame，'column_name'是你想要转换的列名 设置类型
    # df['maturity_date'] = pd.to_datetime(df['maturity_date'])

    df['fund_code'] = df['fund_code'].astype(str)
    df[['fund_code', 'fund_name', 'fund_short_name']] = df[['fund_code', 'fund_name', 'fund_short_name']].astype(str)

    df[['current_nav', 'total_nav', 'daily_change']] = df[['current_nav', 'total_nav', 'daily_change']].replace('', np.nan).astype(float)

    # df['one_week_yield'] = df['one_week_yield'].replace('', np.nan).astype(float).fillna(0.00)
    df['one_week_yield'] = df['one_week_yield'].replace('', np.nan).astype(float)
    # df['one_week_yield'] = df['one_week_yield'].astype(float) / 100

    df[['one_month_yield', 'three_month_yield', 'six_month_yield', 'one_year_yield', 'two_year_yield',
        'three_year_yield', 'year_to_date_yield', 'since_inception_yield']] = df[
        ['one_month_yield', 'three_month_yield', 'six_month_yield', 'one_year_yield', 'two_year_yield',
         'three_year_yield', 'year_to_date_yield', 'since_inception_yield']].replace('', np.nan).astype(float)

    df['ji_shu1'] = df['ji_shu1'].astype(int)
    df[['ji_shu2', 'ji_shu3']] = df[['ji_shu2', 'ji_shu3']].replace('', np.nan).fillna(0).astype(int)
    print("---数据类型---")
    print(df.dtypes)
    print(df.head(10))
    print(df.shape)

    print("----------------------")
    # df["current_nav"] = pd.to_numeric(df["current_nav"], errors='coerce')

    # df = df[df['current_nav'] > 1.3]
    # print(df.shape)
    # print(df['current_nav'])
    # df['fund_code'] = df['fund_code'].astype(str)

from mysql_writer import write_df_to_mysql

write_df_to_mysql(df)
